-- exec sp_standard_actual_comparison_report 'D1010S', '0','0', 120
-- exec sp_standard_cost_report 'D1010S', '0','0', 120
-- exec sp_actual_cost_report 'D1010S', '0','0', 120
ALTER  PROC sp_standard_actual_comparison_report( 
	@FG_PART_NUMBER VARCHAR(50), 	
	@LOTS_DATE_OUT_FROM VARCHAR(20), 
	@LOTS_DATE_OUT_TO VARCHAR(20), 
	@STEP_SIZE INT ) AS
BEGIN
	DECLARE @date_out_start DATETIME
	DECLARE @date_out_end DATETIME

	DECLARE @fab_vendorName VARCHAR(50)
	DECLARE @sort_vendorName VARCHAR(50)
	DECLARE @assembly_vendorName VARCHAR(50)
	DECLARE @test_vendorName VARCHAR(50)
	
	DECLARE @fg_partNumber VARCHAR(50)
	DECLARE @fg_vendorName VARCHAR(50)
	DECLARE @grossDiePerWafer INT

	DECLARE @fab_cycleTime_actual INT
	DECLARE @fab_cost_actual NUMERIC(9, 4)
	DECLARE @fab_yield_actual  NUMERIC(9, 4)
	DECLARE @fab_die_per_wafer_actual INT

	DECLARE @fab_cycleTime_std INT
	DECLARE @fab_cost_std NUMERIC(9, 2)
	DECLARE @fab_yield_std FLOAT
	DECLARE @fab_die_per_wafer_std INT

	DECLARE @sort_cycleTime_actual INT
	DECLARE @sort_cost_actual NUMERIC(9, 2)
	DECLARE @sort_yield_actual NUMERIC(9, 2)
	DECLARE @sort_die_per_wafer_actual INT

	DECLARE @sort_cycleTime_std INT
	DECLARE @sort_cost_std NUMERIC(9, 2)
	DECLARE @sort_yield_std FLOAT
	DECLARE @sort_die_per_wafer_std INT

	DECLARE @assembly_cycleTime_actual INT
	DECLARE @assembly_cost_actual NUMERIC(9, 2)
	DECLARE @assembly_yield_actual  NUMERIC(9, 4)
	DECLARE @assembly_die_per_wafer_actual INT

	DECLARE @assembly_cycleTime_std INT
	DECLARE @assembly_cost_std NUMERIC(9, 2)
	DECLARE @assembly_yield_std FLOAT
	DECLARE @assembly_die_per_wafer_std INT

	DECLARE @test_cycleTime_actual INT
	DECLARE @test_cost_actual NUMERIC(9, 2)
	DECLARE @test_yield_actual  NUMERIC(9, 4)
	DECLARE @test_die_per_wafer_actual INT

	DECLARE @test_cycleTime_std INT
	DECLARE @test_cost_std NUMERIC(9, 2)
	DECLARE @test_yield_std FLOAT
	DECLARE @test_die_per_wafer_std INT

	DECLARE @actual_cost_cursor CURSOR

	DECLARE @RESULTS TABLE( FG_PART_NUMBER VARCHAR(50), GROSS_DIE_WAFER INT, DATE_START DATETIME, DATE_END DATETIME,
				FAB_VENDOR VARCHAR(50), FAB_CYCLE_TIME_STD INT, FAB_YIELD_STD INT, FAB_COST_STD NUMERIC( 9, 2), FAB_CYCLE_TIME_ACTUAL INT, FAB_YIELD_ACTUAL INT, FAB_COST_ACTUAL NUMERIC( 9, 2),
				SORT_VENDOR VARCHAR(50), SORT_CYCLE_TIME_STD INT, SORT_YIELD_STD INT, SORT_COST_STD NUMERIC( 9, 2), SORT_CYCLE_TIME_ACTUAL INT, SORT_YIELD_ACTUAL NUMERIC(9, 2), SORT_COST_ACTUAL NUMERIC( 9, 2),
				ASSEMBLY_VENDOR VARCHAR(50), ASSEMBLY_CYCLE_TIME_STD INT, ASSEMBLY_YIELD_STD INT, ASSEMBLY_COST_STD NUMERIC( 9, 2), ASSEMBLY_CYCLE_TIME_ACTUAL INT, ASSEMBLY_YIELD_ACTUAL NUMERIC(9, 2), ASSEMBLY_COST_ACTUAL NUMERIC( 9, 2),
				TEST_VENDOR VARCHAR(50), TEST_CYCLE_TIME_STD INT, TEST_YIELD_STD INT, TEST_COST_STD NUMERIC( 9, 2), TEST_CYCLE_TIME_ACTUAL INT, TEST_YIELD_ACTUAL NUMERIC(9, 2), TEST_COST_ACTUAL NUMERIC( 9, 2),
				FG_VENDOR VARCHAR(50) )

	DECLARE @ACTUAL_COST TABLE ( FG_PART_NUMBER VARCHAR(50), GROSS_DIE_WAFER INT, DATE_START DATETIME, DATE_END DATETIME,
				FAB_VENDOR VARCHAR(50), FAB_CYCLE_TIME INT, FAB_DIE INT, FAB_COST NUMERIC( 9, 2),
				SORT_VENDOR VARCHAR(50), SORT_CYCLE_TIME INT, SORT_DIE INT, SORT_COST NUMERIC( 9, 2),
				ASSEMBLY_VENDOR VARCHAR(50), ASSEMBLY_CYCLE_TIME INT, ASSEMBLY_DIE INT, ASSEMBLY_COST NUMERIC( 9, 2),
				TEST_VENDOR VARCHAR(50), TEST_CYCLE_TIME INT, TEST_DIE INT, TEST_COST NUMERIC( 9, 2),
				FG_VENDOR VARCHAR(50) )

 	DECLARE @STANDARD_COST TABLE( FG_PART_NUMBER VARCHAR(50), GROSS_DIE_WAFER INT, DATE_START DATETIME, DATE_END DATETIME,
 				FAB_VENDOR VARCHAR(50), FAB_CYCLE_TIME INT, FAB_YIELD NUMERIC( 9, 2), FAB_COST NUMERIC( 9, 2),
 				SORT_VENDOR VARCHAR(50), SORT_CYCLE_TIME INT, SORT_YIELD NUMERIC( 9, 2), SORT_COST NUMERIC( 9, 2),
 				ASSEMBLY_VENDOR VARCHAR(50), ASSEMBLY_CYCLE_TIME INT, ASSEMBLY_YIELD NUMERIC( 9, 2), ASSEMBLY_COST NUMERIC( 9, 2),
 				TEST_VENDOR VARCHAR(50), TEST_CYCLE_TIME INT, TEST_YIELD NUMERIC( 9, 2), TEST_COST NUMERIC( 9, 2),
 				FG_VENDOR VARCHAR(50) )
	
	INSERT @ACTUAL_COST SELECT * FROM fn_actual_cost_info_report( @FG_PART_NUMBER, @LOTS_DATE_OUT_FROM, @LOTS_DATE_OUT_TO, @STEP_SIZE )

	--INSERT @STANDARD_COST SELECT * FROM fn_standard_cost_info_report( @FG_PART_NUMBER, @LOTS_DATE_OUT_FROM, @LOTS_DATE_OUT_TO, @STEP_SIZE )
	SET @actual_cost_cursor = CURSOR FOR SELECT * FROM @ACTUAL_COST
	OPEN @actual_cost_cursor
	FETCH NEXT FROM @actual_cost_cursor INTO @fg_partNumber, @grossDiePerWafer, @date_out_start, @date_out_end, @fab_vendorName, @fab_cycleTime_actual, @fab_die_per_wafer_actual, @fab_cost_actual, @sort_vendorName, @sort_cycleTime_actual, @sort_die_per_wafer_actual, @sort_cost_actual, @assembly_vendorName, @assembly_cycleTime_actual, @assembly_die_per_wafer_actual, @assembly_cost_actual, @test_vendorName, @test_cycleTime_actual, @test_die_per_wafer_actual, @test_cost_actual, @fg_vendorName 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @fab_yield_actual = 100

		SET @fab_die_per_wafer_actual = @grossDiePerWafer
		IF @sort_die_per_wafer_actual IS NOT NULL
		BEGIN
			SELECT @sort_yield_actual = ( CAST ( @sort_die_per_wafer_actual as FLOAT ) / CAST( @fab_die_per_wafer_actual as float ) ) * 100
		END

		IF @sort_die_per_wafer_actual IS NOT NULL AND @sort_die_per_wafer_actual <> 0
		BEGIN
			SELECT @assembly_yield_actual = ( CAST ( @assembly_die_per_wafer_actual as FLOAT ) / CAST( @sort_die_per_wafer_actual as float ) ) * 100
		END
		
		IF @assembly_die_per_wafer_actual IS NOT NULL AND @assembly_die_per_wafer_actual <> 0
		BEGIN
			SELECT @test_yield_actual = ( CAST ( @test_die_per_wafer_actual as FLOAT ) / CAST( @assembly_die_per_wafer_actual as float ) ) * 100
		END

		SELECT @fab_vendorName = FAB_VENDOR, @sort_vendorName = SORT_VENDOR, @assembly_vendorName = ASSEMBLY_VENDOR, @test_vendorName = TEST_VENDOR, @fg_vendorName = FG_VENDOR, @fab_cycleTime_std = FAB_CYCLE_TIME, @fab_yield_std = FAB_YIELD, @fab_cost_std = FAB_COST, @sort_cycleTime_std = SORT_CYCLE_TIME, @sort_yield_std = SORT_YIELD, @sort_cost_std = SORT_COST, @assembly_cycleTime_std = ASSEMBLY_CYCLE_TIME, @assembly_yield_std = ASSEMBLY_YIELD, @assembly_cost_std = ASSEMBLY_COST, @test_cycleTime_std = TEST_CYCLE_TIME, @test_yield_std = TEST_YIELD, @test_cost_std = TEST_COST FROM fn_standard_cost_info_report( @FG_PART_NUMBER, @date_out_start, @date_out_end, @STEP_SIZE )

		INSERT INTO @RESULTS (FG_PART_NUMBER, GROSS_DIE_WAFER, DATE_START, DATE_END, FAB_VENDOR, FAB_CYCLE_TIME_STD, FAB_YIELD_STD, FAB_COST_STD, FAB_CYCLE_TIME_ACTUAL, FAB_YIELD_ACTUAL, FAB_COST_ACTUAL, SORT_VENDOR, SORT_CYCLE_TIME_STD, SORT_YIELD_STD, SORT_COST_STD, SORT_CYCLE_TIME_ACTUAL, SORT_YIELD_ACTUAL, SORT_COST_ACTUAL, ASSEMBLY_VENDOR, ASSEMBLY_CYCLE_TIME_STD, ASSEMBLY_YIELD_STD, ASSEMBLY_COST_STD, ASSEMBLY_CYCLE_TIME_ACTUAL, ASSEMBLY_YIELD_ACTUAL, ASSEMBLY_COST_ACTUAL, TEST_VENDOR, TEST_CYCLE_TIME_STD, TEST_YIELD_STD, TEST_COST_STD, TEST_CYCLE_TIME_ACTUAL, TEST_YIELD_ACTUAL, TEST_COST_ACTUAL, FG_VENDOR ) VALUES	( @fg_partNumber, @grossDiePerWafer, @date_out_start, @date_out_end, @fab_vendorName, @fab_cycleTime_std, @fab_yield_std, @fab_cost_std, @fab_cycleTime_actual, @fab_yield_actual, @fab_cost_actual, @sort_vendorName, @sort_cycleTime_std, @sort_yield_std, @sort_cost_std, @sort_cycleTime_actual, @sort_yield_actual, @sort_cost_actual, @assembly_vendorName, @assembly_cycleTime_std, @assembly_yield_std, @assembly_cost_std, @assembly_cycleTime_actual, @assembly_yield_actual, @assembly_cost_actual, @test_vendorName, @test_cycleTime_std, @test_yield_std, @test_cost_std, @test_cycleTime_actual, @test_yield_actual, @test_cost_actual, @fg_vendorName )

		FETCH NEXT FROM @actual_cost_cursor INTO @fg_partNumber, @grossDiePerWafer, @date_out_start, @date_out_end, @fab_vendorName, @fab_cycleTime_actual, @fab_die_per_wafer_actual, @fab_cost_actual, @sort_vendorName, @sort_cycleTime_actual, @sort_die_per_wafer_actual, @sort_cost_actual, @assembly_vendorName, @assembly_cycleTime_actual, @assembly_die_per_wafer_actual, @assembly_cost_actual, @test_vendorName, @test_cycleTime_actual, @test_die_per_wafer_actual, @test_cost_actual, @fg_vendorName 
	END
	
	CLOSE @actual_cost_cursor
	DEALLOCATE @actual_cost_cursor
		
	SELECT * FROM @RESULTS
END